package com.njzxw.fs.commonutil.web;

import com.njzxw.fs.commonutil.bean.BaseBean;
import com.njzxw.fs.commonutil.bean.Common;
import com.njzxw.fs.commonutil.interfaces.MsmInterface;
import com.njzxw.fs.commonutil.interfaces.OssUtilInterface;
import com.njzxw.fs.commonutil.repository.ResouceReq;
import com.njzxw.fs.commonutil.util.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import javax.annotation.Resource;
import java.io.InputStream;
import java.net.ConnectException;
import java.net.URL;
import java.net.URLConnection;
import java.util.*;

public class BaseServer {

    public org.slf4j.Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 返回参数设定
     */
    @Autowired
    protected ReturnMap returnMap;

    @Resource(name = "aliyunOssImp")
    protected OssUtilInterface ossUtil;

    @Autowired
    protected RedisUtil redisUtil;

    @Autowired
    protected StringHelper stringHelper;

    @Autowired
    protected RequestHelper requestHelper;

    @Autowired
    protected Common common;

    @Resource(name = "requestHelper")
    protected RequestHelper req;

    @Autowired
    protected FtpUtils ftpUtils;

    @Autowired
    protected Validator validator;

    @Autowired
    protected DownloadImage downloadImage;

    @Autowired
    protected VerifyCodeUtils verifyCodeUtils;

    @Autowired
    protected UserUtil userUtil;

    @Autowired
    protected ExcelUtils excelUtils;

    @Autowired
    protected ResouceReq resouceReq;

    @Resource(name = "aliyunMsmImp")
    protected MsmInterface msmInterface;

    public ReturnMap importFile(String fileDir, String importTable, List<String> verifySql, List<String> insertSql,
                                List<Map<String, String>> verifyList, BaseBean baseBean) {

        return importFile(fileDir, importTable, verifySql, insertSql, verifyList, baseBean, null);
    }

    /**
     * 批量导入操作(不要在调用该方法的父级方法设置@Transactional，否则会报错)
     *
     * @param fileDir     导入文件保存到那个目录，如："/user/import/"
     * @param importTable 导入的主表是那个？如：t_manager_user
     * @param verifySql   校验正确性的语句，临时表明使用#tmptable代替，其他语句正常操作
     * @param insertSql   插入数据的语句，第一条请直接插入到 importTable设置的表中，否则会存在问题，临时表明使用#tmptable代替，其他语句正常操作
     * @param verifyList  此字段设置在读取xls文件时就进行正则表达式验证数据正确性 没有传入null
     * @param baseBean    Resouce_id必须存在值
     * @return
     */
    public ReturnMap importFile(String fileDir, String importTable, List<String> verifySql, List<String> insertSql,
                                List<Map<String, String>> verifyList, BaseBean baseBean,
                                ExcelUtils.ImportTableExecuteInterface importTableExecuteInterface) {
        String file_id = ossUtil.exeFile(baseBean.getResouce_id(), fileDir);

        if (StringUtils.isBlank(file_id)) {
            return returnMap.getErrorReturnMap("文件处理失败，未查找到对应上传文件，请重新上传。");
        }

        String table = importTable;

        String tableName = "tmp_import_" + (new Random().nextInt(100000));

        String path = resouceReq.getPath(file_id);
//        logger.debug("path:"+path);
        if (StringUtils.isBlank(path)) {
            return returnMap.getErrorReturnMap("文件可能丢失，未查找到对应上传文件，请重新上传。");
        }

        logger.debug("path::" + path);

        //下载文件
        // 构造URL
        URL url = null;
        InputStream is = null;
        try {
            url = new URL(path);
            // 打开连接
            URLConnection con = url.openConnection();
            //设置请求超时为5s
            con.setConnectTimeout(10 * 1000);
            // 输入流
            is = con.getInputStream();

            List<Map<String, Object>> list = excelUtils.readExcelMap(is, verifyList);
//            logger.debug("list:"+list);
            int listNum = list.size();

            int autoNum = resouceReq.getAutoNum(table);

            //进行临时表sql拼接
            if (list != null && !list.isEmpty()) {
                int cnt = list.get(0).size() - 1;

                String createTbaleSql = "create table " + tableName + "(";

                for (int i = 0; i < cnt; i++) {
                    createTbaleSql += "`col" + i + "` varchar(200) COLLATE utf8_bin DEFAULT NULL,";
                }
                createTbaleSql += "`id` bigint(20) NOT NULL AUTO_INCREMENT,";
                createTbaleSql += "`state` varchar(200) COLLATE utf8_bin DEFAULT NULL,";
                createTbaleSql += "`errorMsg` varchar(200) COLLATE utf8_bin DEFAULT NULL,";
                createTbaleSql += "PRIMARY KEY (`id`) USING BTREE)ENGINE=InnoDB AUTO_INCREMENT=" + autoNum + " " +
                        "DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
                resouceReq.createTmpTable(createTbaleSql);

                resouceReq.updateTableAUtoNum(tableName, autoNum + list.size() + 1);

                //平均插入语句
                String insertSQL = "INSERT INTO " + tableName + "(";
                for (int i = 0; i < cnt; i++) {
                    insertSQL += "`col" + i + "`,";
                    if (i == cnt - 1) {
                        insertSQL += "`errorMsg`";
                    }
                }
                insertSQL += ")values";

                for (int i = 1; i < listNum; i++) {
                    Map<String, Object> map_ = list.get(i);
                    insertSQL += "(";
                    for (int j = 0; j < cnt; j++) {
                        String val1 = StringHelper.get(map_, "col" + j);
                        insertSQL += (StringUtils.isBlank(val1) ? "null" : "'" + val1 + "'") + ",";
                        if (j == cnt - 1) {
                            String val = StringHelper.get(map_, "errorMsg");
                            insertSQL += (StringUtils.isBlank(val) ? "null" : "'" + val + "'");
                        }
                    }
                    if (i == listNum - 1) {
                        insertSQL += ")";
                    } else {
                        insertSQL += "),";
                    }
                }
                //插入到临时表中
                Integer sue = resouceReq.insertInto(insertSQL);

                if (sue < 0) {
                    return returnMap.getErrorReturnMap("数据新增操作错误");
                }

                return excelUtils.importTableList(list, verifySql, insertSql, tableName, importTableExecuteInterface);

            } else {
                return returnMap.getErrorReturnMap("未检测到文件中存在数据");
            }

        } catch (ConnectException conE) {
            conE.printStackTrace();
            return returnMap.getErrorReturnMap("网络可能不顺畅，请重新尝试。");
        } catch (NotOfficeXmlFileException noFileE) {
            noFileE.printStackTrace();
            return returnMap.getErrorReturnMap("您上传的文件可能不是标准的office文件");
        } catch (Exception e) {
            e.printStackTrace();
        }

        return returnMap.getErrorReturnMap("未知错误，请稍后再行尝试");
    }

    /**
     * 单文件，转换bean中Resouce_id对应ID到真实路径 重新放到Resouce_id变量中,如果操作失败，Resouce_id将会变空值
     *
     * @param baseBean
     * @param tableName 无用处
     * @param dir
     * @return
     */
    public void exeResouce(BaseBean baseBean, String tableName, String dir) {
        baseBean.setResouce_id(exeResouce(tableName, dir, baseBean.getResouce_id()));
    }

    /**
     * 单文件，转换bean中Resouce_id对应ID到真实路径 重新放到Resouce_id变量中,如果操作失败，Resouce_id将会变空值
     *
     * @param tableName 无用处
     * @param dir
     * @param pathid    对应临时文件ID
     * @return
     */
    public String exeResouce(String tableName, String dir, String pathid) {
        if (StringUtils.isBlank(pathid)) {
            return null;
        }
        if (!StringUtils.isBlank(pathid) && !pathid.startsWith("y") && !pathid.startsWith("z")) {
            return ossUtil.exeFile(pathid, dir);
        } else if (pathid.startsWith("y")) {//已经存在
            return pathid.replace("y", "");
        } else if (pathid.startsWith("z")) {//正式文件存在
            return pathid.replace("z", "");
        }
        return null;
    }

    /**
     * 单文件，转换bean中Resouce_id对应ID到真实路径 重新放到Resouce_id变量中,如果操作失败，Resouce_id将会变空值
     *
     * @param tableName 无用处
     * @param dir
     * @param pathid    对应临时文件ID
     * @param oper_id   操作人ID
     * @param oper_type 1后台人员 2前端
     * @param oper_name 操作人名称
     * @return
     */
    public String exeResouce(String tableName, String dir, String pathid, String oper_id, int oper_type,
                             String oper_name) {
        if (StringUtils.isBlank(pathid)) {
            return null;
        }
        if (!StringUtils.isBlank(pathid) && !pathid.startsWith("y") && !pathid.startsWith("z")) {
            return ossUtil.exeFile(pathid, dir, oper_id, oper_type, oper_name);
        } else if (pathid.startsWith("y")) {//已经存在
            return pathid.replace("y", "");
        } else if (pathid.startsWith("z")) {//正式文件存在
            return pathid.replace("z", "");
        }
        return null;
    }

    /**
     * 进行多文件处理,操作成功返回null
     *
     * @param baseBean
     * @param tableName
     * @param col_name
     * @param dir
     * @return
     */
    public ReturnMap exeResouces(BaseBean baseBean, String tableName, String col_name, String dir) {
        return exeResouces(baseBean.getResouce_ids(), baseBean.getId(), tableName, col_name, dir);
    }

    /**
     * @param pathsList 资源ID列表
     * @param id        操作表ID
     * @param tableName 操作表名
     * @param col_name  操作表列
     * @param dir       存储路径
     * @return
     */
    public ReturnMap exeResouces(List<String> pathsList, String id, String tableName, String col_name, String dir) {
        if (pathsList != null && !pathsList.isEmpty()) {
            String sql = "delete from " + tableName + " where " + col_name + " = " + id;
            //先进行统一删除操作
            if (resouceReq.delete(sql) < 0) {
                return returnMap.getErrorReturnMap("文件操作失败");
            }
            List<String> ids = new ArrayList<>();
            for (int i = 0; i < pathsList.size(); i++) {
                String pathId = pathsList.get(i);
                if (pathId.startsWith("y")) {//代表是已经存在的关系，直接插入即可不用走图片处理
                    sql = "insert into " + tableName + "(resouce_id," + col_name + ")values(" + (pathId.replace("y",
                            "")) + "," + id + ")";
                    if (resouceReq.insert(sql) <= 0) {
                        return returnMap.getErrorReturnMap("处理失败，请重新上传");
                    }
                } else {
                    ids.add(pathId);
                }
            }

            if (ids.size() != 0) {
                List<String> resultList = ossUtil.exeFile(ids, dir);
                logger.debug("resultList:", resultList);
                if (resultList != null && resultList.size() != ids.size()) {
                    return returnMap.getErrorReturnMap("处理失败，请重新上传");
                }
                for (int i = 0; i < resultList.size(); i++) {
                    sql = "insert into " + tableName + "(resouce_id," + col_name + ")values(" + resultList.get(i) +
                            "," + id + ")";
                    if (resouceReq.insert(sql) <= 0) {
                        return returnMap.getErrorReturnMap("处理失败，请重新上传");
                    }
                }


            }

        }

        return null;
    }

    /**
     * 处理列表中的图片路径信息
     *
     * @param list
     * @param pathName
     * @param resouceIdName
     * @param listPathName
     */
    public void executeListPath(List<Map<String, Object>> list, String pathName, String resouceIdName,
                                String listPathName) {
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            executeMapPath(map, pathName, resouceIdName, listPathName);
        }
    }

    /**
     * 处理详细信息中的图片路径信息
     *
     * @param map
     * @param pathName
     * @param resouceIdName
     * @param listPathName
     */
    public void executeMapPath(Map<String, Object> map, String pathName, String resouceIdName, String listPathName) {

        String paths = StringHelper.get(map, pathName);
        String resouce_ids = StringHelper.get(map, resouceIdName);

        List<Map<String, Object>> list_ = new ArrayList<>();
        if (!StringUtils.isBlank(paths)) {
            String[] path_ = paths.split(",");
            String[] resouce_ids_ = resouce_ids.split(",");
            for (int j = 0; j < path_.length; j++) {
                Map<String, Object> map_ = new HashMap<>();
                map_.put("id", resouce_ids_[j]);
                map_.put("path", path_[j]);
                list_.add(map_);
            }
        }
//        logger.debug("list_",list_);
//        logger.debug("listPathName",listPathName);
        map.put(listPathName, list_);
    }


}
